﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace YJ.Data.MySql
{
    public class ProgramBuilder: YJ.Data.Interface.IProgramBuilder
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public ProgramBuilder()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.ProgramBuilder 实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add(YJ.Data.Model.ProgramBuilder model)
        {
            string sql = @"INSERT INTO programbuilder
				(ID,Name,Type,CreateTime,PublishTime,CreateUser,`SQL`,IsAdd,DBConnID,Status,FormID,EditModel,Width,Height,ButtonLocation,IsPager,ClientScript,ExportTemplate,ExportHeaderText,ExportFileName,TableStyle,TableHead,`TableName`,InDataNumberFiledName) 
				VALUES(@ID,@Name,@Type,@CreateTime,@PublishTime,@CreateUser,@SQL,@IsAdd,@DBConnID,@Status,@FormID,@EditModel,@Width,@Height,@ButtonLocation,@IsPager,@ClientScript,@ExportTemplate,@ExportHeaderText,@ExportFileName,@TableStyle,@TableHead,@TableName,@InDataNumberFiledName)";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = model.ID },
				new MySqlParameter("@Name", MySqlDbType.Text, -1){ Value = model.Name },
				new MySqlParameter("@Type", MySqlDbType.VarChar, 36){ Value = model.Type },
				new MySqlParameter("@CreateTime", MySqlDbType.DateTime, -1){ Value = model.CreateTime },
				model.PublishTime == null ? new MySqlParameter("@PublishTime", MySqlDbType.DateTime, -1) { Value = DBNull.Value } : new MySqlParameter("@PublishTime", MySqlDbType.DateTime, -1) { Value = model.PublishTime },
				new MySqlParameter("@CreateUser", MySqlDbType.VarChar, 36){ Value = model.CreateUser },
				new MySqlParameter("@SQL", MySqlDbType.LongText, -1){ Value = model.SQL },
				new MySqlParameter("@IsAdd", MySqlDbType.Int32, 11){ Value = model.IsAdd },
				new MySqlParameter("@DBConnID", MySqlDbType.VarChar, 36){ Value = model.DBConnID },
				new MySqlParameter("@Status", MySqlDbType.Int32, 11){ Value = model.Status },
				model.FormID == null ? new MySqlParameter("@FormID", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@FormID", MySqlDbType.Text, -1) { Value = model.FormID },
				model.EditModel == null ? new MySqlParameter("@EditModel", MySqlDbType.Int32, 11) { Value = DBNull.Value } : new MySqlParameter("@EditModel", MySqlDbType.Int32, 11) { Value = model.EditModel },
				model.Width == null ? new MySqlParameter("@Width", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@Width", MySqlDbType.VarChar, 50) { Value = model.Width },
				model.Height == null ? new MySqlParameter("@Height", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@Height", MySqlDbType.VarChar, 50) { Value = model.Height },
				model.ButtonLocation == null ? new MySqlParameter("@ButtonLocation", MySqlDbType.Int32, 11) { Value = DBNull.Value } : new MySqlParameter("@ButtonLocation", MySqlDbType.Int32, 11) { Value = model.ButtonLocation },
				model.IsPager == null ? new MySqlParameter("@IsPager", MySqlDbType.Int32, 11) { Value = DBNull.Value } : new MySqlParameter("@IsPager", MySqlDbType.Int32, 11) { Value = model.IsPager },
				model.ClientScript == null ? new MySqlParameter("@ClientScript", MySqlDbType.LongText, -1) { Value = DBNull.Value } : new MySqlParameter("@ClientScript", MySqlDbType.LongText, -1) { Value = model.ClientScript },
				model.ExportTemplate == null ? new MySqlParameter("@ExportTemplate", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@ExportTemplate", MySqlDbType.Text, -1) { Value = model.ExportTemplate },
				model.ExportHeaderText == null ? new MySqlParameter("@ExportHeaderText", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@ExportHeaderText", MySqlDbType.Text, -1) { Value = model.ExportHeaderText },
				model.ExportFileName == null ? new MySqlParameter("@ExportFileName", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@ExportFileName", MySqlDbType.Text, -1) { Value = model.ExportFileName },
                model.TableStyle == null ? new MySqlParameter("@TableStyle", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@TableStyle", MySqlDbType.VarChar, 255) { Value = model.TableStyle },
                model.TableHead == null ? new MySqlParameter("@TableHead", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@TableHead", MySqlDbType.Text, -1) { Value = model.TableHead },
                model.TableName == null ? new MySqlParameter("@TableName", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@TableName", MySqlDbType.VarChar, 255) { Value = model.TableName },
                model.InDataNumberFiledName == null ? new MySqlParameter("@InDataNumberFiledName", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@InDataNumberFiledName", MySqlDbType.VarChar, 255) { Value = model.InDataNumberFiledName }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.ProgramBuilder 实体类</param>
        public int Update(YJ.Data.Model.ProgramBuilder  model)
        {
            string sql = @"UPDATE programbuilder SET 
				Name=@Name,Type=@Type,CreateTime=@CreateTime,PublishTime=@PublishTime,CreateUser=@CreateUser,`SQL`=@SQL,IsAdd=@IsAdd,DBConnID=@DBConnID,Status=@Status,FormID=@FormID,EditModel=@EditModel,Width=@Width,Height=@Height,ButtonLocation=@ButtonLocation,IsPager=@IsPager,ClientScript=@ClientScript,ExportTemplate=@ExportTemplate,ExportHeaderText=@ExportHeaderText,ExportFileName=@ExportFileName,TableStyle=@TableStyle,TableHead=@TableHead,TableName=@TableName,InDataNumberFiledName=@InDataNumberFiledName    
				WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@Name", MySqlDbType.Text, -1){ Value = model.Name },
				new MySqlParameter("@Type", MySqlDbType.VarChar, 36){ Value = model.Type },
				new MySqlParameter("@CreateTime", MySqlDbType.DateTime, -1){ Value = model.CreateTime },
				model.PublishTime == null ? new MySqlParameter("@PublishTime", MySqlDbType.DateTime, -1) { Value = DBNull.Value } : new MySqlParameter("@PublishTime", MySqlDbType.DateTime, -1) { Value = model.PublishTime },
				new MySqlParameter("@CreateUser", MySqlDbType.VarChar, 36){ Value = model.CreateUser },
				new MySqlParameter("@SQL", MySqlDbType.LongText, -1){ Value = model.SQL },
				new MySqlParameter("@IsAdd", MySqlDbType.Int32, 11){ Value = model.IsAdd },
				new MySqlParameter("@DBConnID", MySqlDbType.VarChar, 36){ Value = model.DBConnID },
				new MySqlParameter("@Status", MySqlDbType.Int32, 11){ Value = model.Status },
				model.FormID == null ? new MySqlParameter("@FormID", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@FormID", MySqlDbType.Text, -1) { Value = model.FormID },
				model.EditModel == null ? new MySqlParameter("@EditModel", MySqlDbType.Int32, 11) { Value = DBNull.Value } : new MySqlParameter("@EditModel", MySqlDbType.Int32, 11) { Value = model.EditModel },
				model.Width == null ? new MySqlParameter("@Width", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@Width", MySqlDbType.VarChar, 50) { Value = model.Width },
				model.Height == null ? new MySqlParameter("@Height", MySqlDbType.VarChar, 50) { Value = DBNull.Value } : new MySqlParameter("@Height", MySqlDbType.VarChar, 50) { Value = model.Height },
				model.ButtonLocation == null ? new MySqlParameter("@ButtonLocation", MySqlDbType.Int32, 11) { Value = DBNull.Value } : new MySqlParameter("@ButtonLocation", MySqlDbType.Int32, 11) { Value = model.ButtonLocation },
				model.IsPager == null ? new MySqlParameter("@IsPager", MySqlDbType.Int32, 11) { Value = DBNull.Value } : new MySqlParameter("@IsPager", MySqlDbType.Int32, 11) { Value = model.IsPager },
				model.ClientScript == null ? new MySqlParameter("@ClientScript", MySqlDbType.LongText, -1) { Value = DBNull.Value } : new MySqlParameter("@ClientScript", MySqlDbType.LongText, -1) { Value = model.ClientScript },
				model.ExportTemplate == null ? new MySqlParameter("@ExportTemplate", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@ExportTemplate", MySqlDbType.Text, -1) { Value = model.ExportTemplate },
				model.ExportHeaderText == null ? new MySqlParameter("@ExportHeaderText", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@ExportHeaderText", MySqlDbType.Text, -1) { Value = model.ExportHeaderText },
				model.ExportFileName == null ? new MySqlParameter("@ExportFileName", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@ExportFileName", MySqlDbType.Text, -1) { Value = model.ExportFileName },
                model.TableStyle == null ? new MySqlParameter("@TableStyle", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@TableStyle", MySqlDbType.VarChar, 255) { Value = model.TableStyle },
                model.TableHead == null ? new MySqlParameter("@TableHead", MySqlDbType.Text, -1) { Value = DBNull.Value } : new MySqlParameter("@TableHead", MySqlDbType.Text, -1) { Value = model.TableHead },
                model.TableName == null ? new MySqlParameter("@TableName", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@TableName", MySqlDbType.VarChar, 255) { Value = model.TableName },
                model.InDataNumberFiledName == null ? new MySqlParameter("@InDataNumberFiledName", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@InDataNumberFiledName", MySqlDbType.VarChar, 255) { Value = model.InDataNumberFiledName },
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = model.ID }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(Guid id)
        {
            string sql = "DELETE FROM programbuilder WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = id.ToString() }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<YJ.Data.Model.ProgramBuilder > DataReaderToList(MySqlDataReader dataReader)
        {
            List<YJ.Data.Model.ProgramBuilder > List = new List<YJ.Data.Model.ProgramBuilder >();
            YJ.Data.Model.ProgramBuilder  model = null;
            while (dataReader.Read())
            {
                model = new YJ.Data.Model.ProgramBuilder ();
                model.ID = dataReader.GetString(0).ToGuid();
                model.Name = dataReader.GetString(1);
                model.Type = dataReader.GetString(2).ToGuid();
                model.CreateTime = dataReader.GetDateTime(3);
                if (!dataReader.IsDBNull(4))
                    model.PublishTime = dataReader.GetDateTime(4);
                model.CreateUser = dataReader.GetString(5).ToGuid();
                model.SQL = dataReader.GetString(6);
                model.IsAdd = dataReader.GetInt32(7);
                model.DBConnID = dataReader.GetString(8).ToGuid();
                model.Status = dataReader.GetInt32(9);
                if (!dataReader.IsDBNull(10))
                    model.FormID = dataReader.GetString(10);
                if (!dataReader.IsDBNull(11))
                    model.EditModel = dataReader.GetInt32(11);
                if (!dataReader.IsDBNull(12))
                    model.Width = dataReader.GetString(12);
                if (!dataReader.IsDBNull(13))
                    model.Height = dataReader.GetString(13);
                if (!dataReader.IsDBNull(14))
                    model.ButtonLocation = dataReader.GetInt32(14);
                if (!dataReader.IsDBNull(15))
                    model.IsPager = dataReader.GetInt32(15);
                if (!dataReader.IsDBNull(16))
                    model.ClientScript = dataReader.GetString(16);
                if (!dataReader.IsDBNull(17))
                    model.ExportTemplate = dataReader.GetString(17);
                if (!dataReader.IsDBNull(18))
                    model.ExportHeaderText = dataReader.GetString(18);
                if (!dataReader.IsDBNull(19))
                    model.ExportFileName = dataReader.GetString(19);
                if (!dataReader.IsDBNull(20))
                    model.TableStyle = dataReader.GetString(20);
                if (!dataReader.IsDBNull(21))
                    model.TableHead = dataReader.GetString(21);
                if (!dataReader.IsDBNull(22))
                    model.TableName = dataReader.GetString(22);
                if (!dataReader.IsDBNull(23))
                    model.InDataNumberFiledName = dataReader.GetString(23);
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<YJ.Data.Model.ProgramBuilder > GetAll()
        {
            string sql = "SELECT * FROM programbuilder";
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<YJ.Data.Model.ProgramBuilder > List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM programbuilder";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public YJ.Data.Model.ProgramBuilder  Get(Guid id)
        {
            string sql = "SELECT * FROM programbuilder WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
				new MySqlParameter("@ID", MySqlDbType.VarChar, 36){ Value = id.ToString() }
			};
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.ProgramBuilder > List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 得到分页数据
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<YJ.Data.Model.ProgramBuilder> GetList(out string pager, string query = "", string name = "", string typeid="")
        {
            List<MySqlParameter> parList = new List<MySqlParameter>();
            StringBuilder sql = new StringBuilder("SELECT * FROM ProgramBuilder WHERE Status<>2 ");

            if (!name.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Name,@Name)>0");
                parList.Add(new MySqlParameter("@Name", name));
            }

            if (!typeid.IsNullOrEmpty())
            {
                sql.Append(" AND Type IN(" + Utility.Tools.GetSqlInString(typeid) + ")");
            }

            sql.Append(" ORDER BY CreateTime DESC");

            long count;
            int size = YJ.Utility.Tools.GetPageSize();
            int number = YJ.Utility.Tools.GetPageNumber();
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            pager = YJ.Utility.Tools.GetPagerHtml(count, size, number, query);
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            List<YJ.Data.Model.ProgramBuilder> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }


        


    }
}